﻿#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PhotoGallery.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace PhotoGallery.DALProviders.SqlServerProvider
{
    public class RoleProvider : PhotoGallery.Core.Providers.DALProvider.IRoleProvider
    {
        #region Sql
        private static readonly string SqlSelectRole = "SELECT [Id], [Status],[AddDate],[Name],[Description] FROM [Role] WHERE  [Id]=@Id;";

        private static readonly string SqlInsertRole = "INSERT INTO [Role]([Status],[Name],[Description]) values(@Status,@Name,@Description);SELECT SCOPE_IDENTITY();";

        private static readonly string SqlUpdateRole = "UPDATE [Role] SET [Status] = @Status,[AddDate] = @AddDate,[Name] = @Name,[Description] = @Description WHERE  [Id]=@Id;";

        private static readonly string SqlDeleteRole = "DELETE [Role] WHERE  [Id]=@Id;";

        private static readonly string SqlGetAllRole = "SELECT [Id], [Status],[AddDate],[Name],[Description] FROM [Role];";

        private static readonly string SqlFind = "SELECT [Id], [Status],[AddDate],[Name],[Description] FROM [Role] WHERE [Name]=@Name ;";

        #endregion

        #region Procedures
        /*

-- Begin of Role
IF OBJECT_ID(N'[USP_Role_Delete_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Role_Delete_By_Id]

IF OBJECT_ID(N'[USP_Role_Select_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Role_Select_By_Id]

IF OBJECT_ID(N'[USP_Role_Insert]') IS NOT NULL
	DROP PROCEDURE [USP_Role_Insert]

IF OBJECT_ID(N'[USP_Role_Update_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Role_Update_By_Id]

IF OBJECT_ID(N'[USP_Role_SelectAll]') IS NOT NULL
	DROP PROCEDURE [USP_Role_SelectAll]

GO

--region [USP_Role_Delete_By_Id]
CREATE PROCEDURE [USP_Role_Delete_By_Id]
	@Id	int
AS
SET NOCOUNT ON
DELETE [Role] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Role_Select_By_Id]
CREATE PROCEDURE [USP_Role_Select_By_Id]
	@Id	int
AS
SET NOCOUNT ON
SELECT [Id], [Status],[AddDate],[Name],[Description] FROM [Role] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Role_Insert]
CREATE PROCEDURE [USP_Role_Insert]
	@Status	int,
	@AddDate	datetime,
	@Name	nvarchar(30),
	@Description	nvarchar(100)
AS
SET NOCOUNT ON
INSERT INTO [Role]([Status],[AddDate],[Name],[Description]) values(@Status,@AddDate,@Name,@Description);SELECT SCOPE_IDENTITY();
--endregion
GO

--region [USP_Role_Update_By_Id]
CREATE PROCEDURE [USP_Role_Update_By_Id]
	@Status	int,
	@AddDate	datetime,
	@Name	nvarchar(30),
	@Description	nvarchar(100),
	@Id	int
AS
SET NOCOUNT ON
UPDATE [Role] SET [Status] = @Status,[AddDate] = @AddDate,[Name] = @Name,[Description] = @Description WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Role_SelectAll]
CREATE PROCEDURE [USP_Role_SelectAll]
AS
SET NOCOUNT ON
SELECT [Id], [Status],[AddDate],[Name],[Description] FROM [Role];
--endregion
GO


-- End of Role
*/
        #endregion

        #region IRoleProvider Members
        public Core.Business.Role Select(int id)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, id);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectRole);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.Role role = new Core.Business.Role();

                if (!reader.IsDBNull(0)) role.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) role.Status = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) role.AddDate = reader.GetDateTime(2);
                if (!reader.IsDBNull(3)) role.Name = reader.GetString(3);
                if (!reader.IsDBNull(4)) role.Description = reader.GetString(4);

                reader.Close();
                return role;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public void Insert(Core.Business.Role role)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Status", SqlDbType.Int, role.Status);
            //sql.AddParameter("@AddDate", SqlDbType.DateTime, role.AddDate);
            sql.AddParameter("@Name", SqlDbType.NVarChar, role.Name);
            sql.AddParameter("@Description", SqlDbType.NVarChar, role.Description);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertRole);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                if (!reader.IsDBNull(0)) role.Id = int.Parse(reader.GetDecimal(0).ToString());

                reader.Close();
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }

        public void Update(Core.Business.Role role)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Status", SqlDbType.Int, role.Status);
            sql.AddParameter("@AddDate", SqlDbType.DateTime, role.AddDate);
            sql.AddParameter("@Name", SqlDbType.NVarChar, role.Name);
            sql.AddParameter("@Description", SqlDbType.NVarChar, role.Description);
            sql.AddParameter("@Id", SqlDbType.Int, role.Id);
            sql.ExecuteSql(SqlUpdateRole);
        }

        public void Delete(Core.Business.Role role)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, role.Id);
            sql.ExecuteSql(SqlDeleteRole);
        }

        public IList<Core.Business.Role> GetAllRole()
        {
            IList<Core.Business.Role> rolelist = new List<Core.Business.Role>();
            SqlServerUtility sql = new SqlServerUtility();

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllRole);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.Role role = new Core.Business.Role();

                    if (!reader.IsDBNull(0)) role.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) role.Status = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) role.AddDate = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3)) role.Name = reader.GetString(3);
                    if (!reader.IsDBNull(4)) role.Description = reader.GetString(4);

                    role.MarkOld();
                    rolelist.Add(role);
                }
                reader.Close();
            }
            return rolelist;
        }
        #endregion


        public ICollection<Role> GetAllRoles()
        {
            return this.GetAllRole();
        }


        public Role Find(string name)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Name", SqlDbType.NVarChar, name);

            SqlDataReader reader = sql.ExecuteSqlReader(SqlFind);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.Role role = new Core.Business.Role();

                if (!reader.IsDBNull(0)) role.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) role.Status = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) role.AddDate = reader.GetDateTime(2);
                if (!reader.IsDBNull(3)) role.Name = reader.GetString(3);
                if (!reader.IsDBNull(4)) role.Description = reader.GetString(4);

                reader.Close();
                return role;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }
    }
}
